﻿using System;
using System.Collections;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Data.SqlTypes;
using System.IO;

namespace nextGeneration_app
{
    public class teacher_dbManager
    {
        static string CONNECTIONSTRING = ConfigurationManager.ConnectionStrings["nextgenerationConnectionString"].ToString();
        // public const string CONNECTIONSTRING = "Server=localhost;Database=dbnextgeneration;Trusted_Connection=true";

        public static bool teacherLogin(string userID, string userPassword)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;

            try
            {
                //step 1 : Connect to Database
                conn.Open();

                //step 2: Prepare the SQL Command
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "SELECT userID,userPassword From programUser WHERE (userID=@userID) AND (userPassword=@userPassword) ";
                comm.Parameters.AddWithValue("@userID", userID);
                comm.Parameters.AddWithValue("@userPassword", userPassword);
                comm.Connection = conn;

                //setp 3: Execute the SQL Command

                //Select ExecuteReader

                SqlDataReader dr = comm.ExecuteReader();
                result = dr.Read();
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;

        }
        public static string checkTypeOfUser(string userID, string userPassword)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;
            string type=null;
            try
            {
                //step 1 : Connect to Database
                conn.Open();

                //step 2: Prepare the SQL Command
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT userType From programUser WHERE (userID=@userID) AND (userPassword=@userPassword) ";
                comm.Parameters.AddWithValue("@userID", userID);
                comm.Parameters.AddWithValue("@userPassword", userPassword);


                //setp 3: Execute the SQL Command

                //Select ExecuteReader

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    type = dr["userType"].ToString();
                }
                dr.Close();
                
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return type;
        }
        public static bool teacherforgotPassword(string jspassword, string jsemail)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;
            try
            {
                //step 1: Connect to database
                conn.Open();

                //step 2: Prepare the SQL Command
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE programUser SET userPassword=@jspassword WHERE userEmail=@email";
                comm.Parameters.AddWithValue("@jspassword", jspassword);
                comm.Parameters.AddWithValue("@email", jsemail);

                //step 3: Execute the SQL Command
                SqlDataReader dr = comm.ExecuteReader();

                result = dr.Read();

            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }
        public static void confirmation(confirmation con)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.Open();
                conn.ConnectionString = CONNECTIONSTRING;

                SqlCommand com = new SqlCommand();
                com.Connection = conn;
                string query = "";
                query += "INSERT INTO confirmation(confirmationcode)";
                query += "VALUES (@con)";
                com.CommandText = query;

                com.Parameters.AddWithValue("@con", con.Confirmation);
                int rowsAdded = (int)com.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
        public static bool confirmationCode(string code)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.Open();
                conn.ConnectionString = CONNECTIONSTRING;
                SqlCommand com = new SqlCommand();
                com.Connection = conn;

                string query = "SELECT confirmationcode FROM confirmation";
                query += "WHERE (confirmationcode=@code)";
                com.CommandText = query;
                com.Parameters.AddWithValue("@code", code);

                SqlDataReader dr = com.ExecuteReader();

                result = dr.Read();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {

                conn.Close();
            }
            return result;

        }
        public static bool deleteConfirmationCode(string dlCode)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.Open();
                conn.ConnectionString = CONNECTIONSTRING;

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                string query = "DELETE FROM confirmation";
                query += "WHERE (confirmationcode=@concode)";
                comm.CommandText = query;
                comm.Parameters.AddWithValue("@concode", dlCode);

                int rowAffected = comm.ExecuteNonQuery();
                if (rowAffected > 0)
                {
                    return result == true;
                }

                else
                {
                    return result;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();

            }
        }

        public static teacher_basicInfo getUserBasicInfo(string userID)
        {
            teacher_basicInfo info = null;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;
            try
            {
                //1. Open The Connection
                conn.Open();

                //2. Prepare the SQL Command
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "SELECT userID,userName,userEmail,userRegisterDate FROM programUser WHERE (userID=@userID)";
                comm.Parameters.AddWithValue("@userID", userID);
                comm.Connection = conn;

                //3. Execute the SQL Command
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    string userName = dr["userName"].ToString();
                    string userEmail = dr["userEmail"].ToString();
                    DateTime registerDate = Convert.ToDateTime(dr["userRegisterDate"].ToString());

                    info = new teacher_basicInfo(userID, userName, userEmail, registerDate);
               }
                dr.Close();
                return info;

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);

            }
            finally
            {
                conn.Close();
            }

        }
        public static Stream DisplayImage(int userID)
        {
            ArrayList imgList = new ArrayList();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;
            byte[] binary = new byte[0];

            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "SELECT teacherPhoto FROM teacherUser WHERE userID=@userID";
                comm.Parameters.AddWithValue("@userID", userID);
                comm.Connection = conn;
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    string content = dr["image"].ToString();
                    binary = (byte[])dr["image"];
                    imgList.Add(content);
                    imgList.Add(binary);

                }
                return new MemoryStream((byte[])binary);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
        public static void insert_teacherInfo(int userID, teacherUser teacher)
        {

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                string query = "";
                query += "INSERT INTO teacherUser(teacherPassport,teacherPhoto,teacherFin,teacherGender,teacherDob,teacherRace,teacherReligion,teacherMaritalStatus,teacherCitizen,teacherCountryOfBirth)";
                query += "VALUES                  (@teacherPassport,@teacherFin,@teacherGender,@teacherDob,@teacherRace,@teacherReligion,@teacherMaritalStatus,@teacherCitizen,@teacherCountryOfBirth)";
                query += "WHERE                   (userID=@userID)";
                comm.CommandText = query;

                comm.Parameters.AddWithValue("@userID", userID);
                SqlParameter pictureParameter = new SqlParameter("@teacherPhoto", SqlDbType.Image);
                pictureParameter.Value = teacher.Image;
                comm.Parameters.Add(pictureParameter);               
                
            
                comm.Parameters.AddWithValue("@teacherPassport", teacher.Passport);
                comm.Parameters.AddWithValue("@teacherFin", teacher.Fin);
                comm.Parameters.AddWithValue("@teacherGender", teacher.Gender);
                comm.Parameters.AddWithValue("@teacherDob", teacher.DOB);
                comm.Parameters.AddWithValue("@teacherRace", teacher.Race);
                comm.Parameters.AddWithValue("@teacherReligion", teacher.Religion);
                comm.Parameters.AddWithValue("@teacherMaritalStatus", teacher.MaritalStatus);
                comm.Parameters.AddWithValue("@teacherCitizen", teacher.Citizen);
                comm.Parameters.AddWithValue("@teacherCountryOfBirth", teacher.BirthCountry);

                int rowsAdded = (int)comm.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
        public static teacherUser retrieve_teacherInfo(string userID)
        {
            teacherUser teacher = null;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;

            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "SELECT userID, teacherPassport,teacherFinNumber,teacherGender,teacherDob,teacherRace,teacherReligion,teacherMaritalStatus,teacherCitizen,teacherCountryOfBirth FROM teacherUser WHERE userID=@userID";
                comm.Parameters.AddWithValue("@userID",userID);

               SqlDataReader dr = comm.ExecuteReader();

                while(dr.Read())
                {
                    string teacherPassport = dr["teacherPassport"].ToString();
                    string teacherFin = dr["teacherFinNumber"].ToString();
                    string teacherGender = dr["teacherGender"].ToString();
                    DateTime teacherDob = Convert.ToDateTime(dr["teacherDob"].ToString());
                    string teacherRace = dr["teacherRace"].ToString();
                    string teacherReligion = dr["teacherReligion"].ToString();
                    string teacherMaritalStatus = dr["teacherMaritalStatus"].ToString();
                    string teacherCitizen = dr["teacherCitizen"].ToString();
                    string teacherCountryOfBirth = dr["teacherCountryOfBirth"].ToString();
              
                    teacher = new teacherUser(userID, teacherPassport, teacherFin, teacherGender, teacherDob, teacherRace, teacherReligion, teacherMaritalStatus, teacherCitizen, teacherCountryOfBirth);
                }
                return teacher;          
                


            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }

        }
        public static teacherAddress getTeacherAddress(string userID)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString=CONNECTIONSTRING;
            teacherAddress address = null;
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT userID,homeBlkNumber,homeUnitNumber,streetName,stateName,country,handPhoneNumber,telephoneNumber,postalCode FROM userAddress WHERE userID=@userID";
                comm.Parameters.AddWithValue("userID", userID);
                
                SqlDataReader dr=comm.ExecuteReader();
                while (dr.Read())
                {
                    string homeBlkNumber = dr["homeBlkNumber"].ToString();
                    string homeUnitNumber = dr["homeUnitNumber"].ToString();
                    string streetName = dr["streetName"].ToString();
                    string stateName=dr["stateName"].ToString();
                    string country = dr["country"].ToString();
                    int handphoneNumber = Convert.ToInt32(dr["handPhoneNumber"].ToString());
                    int telephoneNumber = Convert.ToInt32(dr["telephoneNumber"].ToString());
                    int postalCode = Convert.ToInt32(dr["postalCode"].ToString());

                    address = new teacherAddress(userID, homeBlkNumber, homeUnitNumber, streetName, stateName,country, handphoneNumber, telephoneNumber, postalCode);
                }
                dr.Close();
                return address;
            }
         

            catch(Exception Exception)
            {
                throw new Exception(Exception.Message);
            }
            finally
            {
                conn.Close();
            }

        }
        public static void insert_teacherAddress(int userID, teacherAddress address)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();

            }
            catch (Exception ex)
            {

            }
            finally
            {
                conn.Close();
            }
 
        }

        public static Boolean updateTeacherInfo(string userID,teacherUser teacher)
        {
            bool result=false;
            SqlConnection conn=new SqlConnection();
            conn.ConnectionString=CONNECTIONSTRING;
            try
            {
                conn.Open();
                SqlCommand comm=new SqlCommand();
                comm.Connection=conn;

                comm.CommandText="UPDATE teacherUser SET teacherPhoto=@teacherPhoto,teacherPassport=@teacherPassport,teacherFinNumber=@teacherFinNumber,teacherRace=@teacherRace,teacherReligion=@teacherReligion,teacherMaritalStatus=@teacherMaritalStatus,teacherCitizen=@teacherCitizen,teacherCountryOfBirth=@teacherCountryOfBirth WHERE userID=@uesrID";
                comm.Parameters.AddWithValue("@userID",userID);
                comm.Parameters.AddWithValue("@teacherPhoto",teacher.Image);
                comm.Parameters.AddWithValue("@teacherPassport",teacher.Passport);
                comm.Parameters.AddWithValue("@teacherFinNumber",teacher.Fin);
                comm.Parameters.AddWithValue("@teacherRace",teacher.Race);
                comm.Parameters.AddWithValue("@teacherReligion",teacher.Religion);
                comm.Parameters.AddWithValue("@teacherCountryOfBirth",teacher.BirthCountry);

                int rowsUpdated=(int)comm.ExecuteNonQuery();

                if(rowsUpdated>0)
                {
                    result=true;
                }
                else
                {
                    result=false;
                }             
            }

            catch(Exception ex)
            {
                throw new Exception (ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
       
        }
        public static string getUserEmail(string userID)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;
            string email = null;
            
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "SELECT userEmail FROM programUser WHERE userID=@userID";
                comm.Parameters.AddWithValue("@userID", userID);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    email = dr["userEmail"].ToString();
                }
                dr.Close();
            }

            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return email;
        }
    }
}
                               

    
    

